 /**
   * Copyright 2014 Viettel Telecom. All rights reserved.
   * VIETTEL PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
   */
 package com.viettel.core.dao.cms; 
import java.util.ArrayList;
import java.util.List;

import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;

import com.viettel.core.common.utils.StringUtil;
import com.viettel.core.entities.cms.Article;
import com.viettel.core.entities.cms.AssignQuestion;
import com.viettel.core.entities.cms.CmsUser;
import com.viettel.core.entities.cms.GroupCategory;
import com.viettel.core.entities.cms.Media;
import com.viettel.core.entities.cms.Questions;
import com.viettel.core.entities.cms.Users;
import com.viettel.core.entities.vo.QuestionSearchFilter;
import com.viettel.core.entities.vo.QuestionSearchVO;
import com.viettel.core.entities.vo.QuestionUserVO;
import com.viettel.core.enumtype.QuestionsStatus;
import com.viettel.core.enumtype.UserStatus;
import com.viettel.core.exceptions.DataAccessException;
import com.viettel.core.repo.IRepository;
 /**
   *  Mo ta muc dich cua lop (interface)
   *  @author: TOANTM1
   *  @version:  
   *  @since: 27/02/2014 - 11:34:02
   */
 public class QuestionsDAOImpl implements QuestionsDAO { 
	 private IRepository repo; 
		
	 	public void setRepo(IRepository repo) {
			this.repo = repo;
		}
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public Questions getQuestionsById(Integer id) throws DataAccessException { 
		return repo.getEntityById(Questions.class, id); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public Questions createQuestions(Questions questions) 
			throws DataAccessException { 
		if (questions == null) { 
			throw new IllegalArgumentException("questions"); 
		} 
		repo.create(questions); 
		return repo.getEntityById(Questions.class, questions.getId()); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void updateQuestions(Questions questions) throws DataAccessException { 
		if (questions == null) { 
			throw new IllegalArgumentException("questions"); 
		} 
		repo.update(questions); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void deleteQuestions(Questions questions) throws DataAccessException { 
		if (questions == null) { 
			throw new IllegalArgumentException("questions"); 
		} 
		repo.delete(questions); 
	}

	@Override
	public List<QuestionSearchVO> getListQuestionByFilter(
			QuestionSearchFilter filter) throws DataAccessException {
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
		
		selectSql.append(" SELECT QU.id  			as id, ");
		selectSql.append(" QU.content 				as content, ");
		selectSql.append(" QU.content 				as contentText, ");
		selectSql.append(" QU.status 				as status, ");
		selectSql.append(" QU.status 				as statusCheck, ");
		selectSql.append(" GC.id 					as groupCategoryId, ");
		selectSql.append(" GC.name 					as groupCategoryName, ");
		selectSql.append(" GC.parent_id				as groupCategoryParentId, ");
		selectSql.append(" TE.id 					as techniqueCategoryId, ");
		selectSql.append(" TE.name 					as techniqueCategoryName, ");
		selectSql.append(" TE.parent_id				as techniqueCategoryParentId, ");
		selectSql.append(" ME.id 					as mediaId, ");
		selectSql.append(" ME.name 					as mediaName, ");
		selectSql.append(" US.name 					as userName, ");
		selectSql.append(" US.id 					as userId, ");
		selectSql.append(" US.phone 				as userPhone, ");
		selectSql.append(" PR.id					as provinceId, ");
		selectSql.append(" PR.name					as provinceName, ");
		selectSql.append(" DI.id					as districtId, ");
		selectSql.append(" DI.name					as districtName, ");
		selectSql.append(" WA.id					as wardId, ");
		selectSql.append(" WA.name					as wardName, ");
		selectSql.append(" CM.id 					as createdUserId, ");
		selectSql.append(" CM.name 					as createdUser, ");
		selectSql.append(" QU.created_time 			as createdTime, ");
		selectSql.append(" QU.updated_time 			as updatedTime, ");
		selectSql.append(" AQ.updated_time 			as updatedTimeAQ, ");
		selectSql.append(" QU.answer 				as answer, ");
		selectSql.append(" QU.answer 				as answerText, ");
		selectSql.append(" CMSU.id		 			as assignTo, ");
		selectSql.append(" CMSU.name	 			as assignToName, ");
		selectSql.append(" AR.id 					as articleAnswerId, ");
		selectSql.append(" AR.type 					as articleAnswerType, ");
		selectSql.append(" AR.name 					as articleAnswerName, ");
		selectSql.append(" AQ.count 				as count ");
		
		fromQuery.append(" FROM QUESTIONS QU ");
		fromQuery.append(" LEFT JOIN GROUP_CATEGORY GC ");
		fromQuery.append(" 		ON QU.group_category_id = GC.id ");
		fromQuery.append(" LEFT JOIN TECHNIQUE_CATEGORY TE ");
		fromQuery.append(" 		ON QU.technique_category_id = TE.id ");
		fromQuery.append(" LEFT JOIN MEDIA ME ");
		fromQuery.append(" 		ON QU.media_id = ME.id ");
		fromQuery.append(" LEFT JOIN CMS_USER CM ");
		fromQuery.append(" 		ON QU.cms_user_id = CM.id ");
		fromQuery.append(" LEFT JOIN USERS US ");
		fromQuery.append(" 		ON QU.user_id = US.id ");
		fromQuery.append(" LEFT JOIN ASSIGN_QUESTION AQ ");
		fromQuery.append(" 		ON AQ.question_id = QU.id AND AQ.STATUS != -1 ");
		fromQuery.append(" LEFT JOIN PROVINCE PR ");
		fromQuery.append(" 		ON US.province_id = PR.id ");
		fromQuery.append(" LEFT JOIN DISTRICT DI ");
		fromQuery.append(" 		ON US.district_id = DI.id ");
		fromQuery.append(" LEFT JOIN WARD WA ");
		fromQuery.append(" 		ON US.ward_id = WA.id ");
		fromQuery.append(" LEFT JOIN ARTICLE AR ");
		fromQuery.append(" 		ON QU.article_answer_id = AR.id ");
		fromQuery.append(" LEFT JOIN CMS_USER CMSU ");
		fromQuery.append(" 		ON AQ.assign_to = CMSU.id ");
		
		fromQuery.append(" where QU.status != ? and US.status != ? ");
		paramSelect.add(QuestionsStatus.DELETED.getValue());
		countParams.add(QuestionsStatus.DELETED.getValue());
		paramSelect.add(UserStatus.DELETED.getValue());
		countParams.add(UserStatus.DELETED.getValue());
		
		if (filter.getCmsUserId() != null) {
			fromQuery.append(" and AQ.assign_to = ? ");
			paramSelect.add(filter.getCmsUserId());
			countParams.add(filter.getCmsUserId());
		}
		
		if (filter.getId() != null) {
			fromQuery.append(" and QU.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}
		if (filter.getContent() != null) {
			fromQuery.append(" and (upper(QU.content_text) like upper(?) ESCAPE '/' ) ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getContent())+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getContent())+ "%");
		}
		if(filter.getIsUserQuestion() != null && filter.getIsUserQuestion()==true){
			if (filter.getUserPhone() != null) {
				fromQuery.append(" and US.phone = ? ");
				paramSelect.add(filter.getUserPhone());
				countParams.add(filter.getUserPhone());
			}
		}else{
			if (filter.getUserPhone() != null) {
				fromQuery.append(" and (upper(US.phone) like upper(?) ESCAPE '/' ) ");
				paramSelect.add("%" + StringUtil.toSearchText(filter.getUserPhone())+ "%");
				countParams.add("%" + StringUtil.toSearchText(filter.getUserPhone())+ "%");
			}
		}
		if (filter.getUserName() != null) {
			fromQuery.append(" and (upper(US.name_text) like upper(?) ESCAPE '/' ) ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getUserName())+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getUserName())+ "%");
		}
		if (filter.getGroupCategoryId() != null) {
			fromQuery.append(" and ( GC.ID = ? OR GC.PARENT_ID = ? )");
			paramSelect.add(filter.getGroupCategoryId());
			countParams.add(filter.getGroupCategoryId());
			paramSelect.add(filter.getGroupCategoryId());
			countParams.add(filter.getGroupCategoryId());
		}
		
		if (filter.getTechniqueCategoryId() != null) {
			fromQuery.append(" and ( TE.ID = ? OR TE.PARENT_ID = ? ) ");
			paramSelect.add(filter.getTechniqueCategoryId());
			countParams.add(filter.getTechniqueCategoryId());
			paramSelect.add(filter.getTechniqueCategoryId());
			countParams.add(filter.getTechniqueCategoryId());
		} 
		if (filter.getMediaId() != null) {
			fromQuery.append(" and ME.ID = ? ");
			paramSelect.add(filter.getMediaId());
			countParams.add(filter.getMediaId());
		}
		if (filter.getUserId() != null) {
			fromQuery.append(" and US.ID = ? ");
			paramSelect.add(filter.getUserId());
			countParams.add(filter.getUserId());
		}
		if (filter.getStatus() != null) {
			fromQuery.append(" and QU.status = ? ");
			paramSelect.add(filter.getStatus());
			countParams.add(filter.getStatus());
		}
		if(filter.getFromDate()!=null){
			fromQuery.append("  AND TRUNC(QU.created_time) >= TRUNC(?) ");
			paramSelect.add(filter.getFromDate());
			countParams.add(filter.getFromDate());
		}
		if(filter.getToDate()!=null ){
			fromQuery.append("  AND TRUNC(QU.created_time) <= TRUNC(?) ");		
			paramSelect.add(filter.getToDate());
			countParams.add(filter.getToDate());
		}
		if (filter.getProvinceId()!=null) {
			fromQuery.append(" and US.PROVINCE_ID = ? ");
			paramSelect.add(filter.getProvinceId());
			countParams.add(filter.getProvinceId());
		}
		if (filter.getDistrictId()!=null) {
			fromQuery.append(" and US.DISTRICT_ID = ? ");
			paramSelect.add(filter.getDistrictId());
			countParams.add(filter.getDistrictId());
		}
		
		if (filter.getWardId()!=null) {
			fromQuery.append(" and US.WARD_ID = ? ");
			paramSelect.add(filter.getWardId());
			countParams.add(filter.getWardId());
		}
		
		if (filter.getIsOverTime() != null && filter.getIsOverTime()==true ) {
			fromQuery.append(" and (( QU.status = ? and AQ.UPDATED_TIME + 2 <= systimestamp) or (QU.status = ? AND QU.UPDATED_TIME + 2 <= systimestamp)) ");
			paramSelect.add(QuestionsStatus.CONSIGN.getValue());
			countParams.add(QuestionsStatus.CONSIGN.getValue());
			paramSelect.add(QuestionsStatus.NOT_ANSWER.getValue());
			countParams.add(QuestionsStatus.NOT_ANSWER.getValue());
			
			selectSql.append(fromQuery).append(" ORDER BY QU.updated_time ASC");
		} else {
			selectSql.append(fromQuery).append(" ORDER BY QU.updated_time DESC, QU.created_time DESC");
		}
		
		
		countQuery.append("SELECT COUNT(*) AS COUNT ").append(fromQuery);

		String[] fieldNames = new String[] { 
				"id","content", "contentText", 
				"status", "statusCheck",
				"groupCategoryId", "groupCategoryName", "groupCategoryParentId",
				"techniqueCategoryId", "techniqueCategoryName", "techniqueCategoryParentId",
				"mediaId", "mediaName",
				"userName", "userId", "userPhone", 
				"provinceId", "provinceName", 
				"districtId", "districtName",
				"wardId", "wardName",
				"createdUserId", "createdUser", 
				"createdTime", "updatedTime", "updatedTimeAQ",
				"answer","answerText",
				"assignTo", "assignToName",
				"articleAnswerId", "articleAnswerType", "articleAnswerName",
				"count"
				};
		
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER,
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, StandardBasicTypes.INTEGER,
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, StandardBasicTypes.INTEGER,
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING,
				StandardBasicTypes.STRING, StandardBasicTypes.INTEGER, StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, 
				StandardBasicTypes.TIMESTAMP, StandardBasicTypes.TIMESTAMP, StandardBasicTypes.TIMESTAMP,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER, StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER
		};

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(Questions.class);
		synchronizedClass.add(GroupCategory.class);
		synchronizedClass.add(CmsUser.class);
		synchronizedClass.add(Users.class);
		synchronizedClass.add(AssignQuestion.class);
		synchronizedClass.add(Article.class);
		synchronizedClass.add(Media.class);

		List<QuestionSearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					QuestionSearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(QuestionSearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}

		return list;
	}

	@Override
	public List<QuestionUserVO> getListQuestionByPhone(String phone, Integer maxRows)
			throws DataAccessException {
		List<Object> params = new ArrayList<Object>();
		StringBuilder selectQuery = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		
		selectQuery.append(" select user_name as userName,user_phone as userPhone,user_email as userEmail ");
		selectQuery.append(" from questions ");
		selectQuery.append(" where user_phone is not null ");
		selectQuery.append(" and user_phone like ? ");
		params.add("%" + phone + "%");
		if(maxRows!=null){
			selectQuery.append(" and rownum < ? ");
			params.add(maxRows);
		}
		selectQuery.append(" group by user_name,user_phone,user_email ");
		countQuery.append("SELECT COUNT(*) AS COUNT FROM ( ").append(selectQuery).append(" )");

		String[] fieldNames = new String[] { 
				"userName", "userPhone","userEmail"
				};
		
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.STRING,StandardBasicTypes.STRING,
				StandardBasicTypes.STRING
		};

		List<QuestionUserVO> list = null;
		return list = repo.getListByQueryAndScalar(QuestionUserVO.class,
				fieldNames, fieldTypes, selectQuery.toString(), params);
	} 
	public List<Questions> getListQuestionArticleId(Integer id) throws DataAccessException{
		List<Object> params = new ArrayList<Object>();
		String sql = "SELECT * FROM QUESTIONS  where STATUS != ? AND ARTICLE_ANSWER_ID = ?";
		params.add(QuestionsStatus.DELETED.getValue());
		params.add(id);
		return repo.getListBySQL(Questions.class, sql, params);
	}
	@Override
	public List<Questions> getListQuestionsByCmsUserId(Integer id) throws DataAccessException{
		List<Object> params = new ArrayList<Object>();
		String sql = "SELECT * FROM QUESTIONS  where STATUS != ? AND CMS_USER_ID = ?";
		params.add(QuestionsStatus.DELETED.getValue());
		params.add(id);
		return repo.getListBySQL(Questions.class, sql, params);
	}
	@Override
	public List<Questions> getListQuestionsByUserId(Integer id) throws DataAccessException{
		List<Object> params = new ArrayList<Object>();
		String sql = "SELECT * FROM QUESTIONS  where STATUS != ? AND USER_ID = ?";
		params.add(QuestionsStatus.DELETED.getValue());
		params.add(id);
		return repo.getListBySQL(Questions.class, sql, params);
	}
	@Override
	public List<Questions> getListQuestionsNearOverTime() throws DataAccessException{
		List<Object> params = new ArrayList<Object>();
		String sql = "SELECT QU.*,AQ.UPDATED_TIME FROM QUESTIONS QU ";
		sql += "	LEFT JOIN ASSIGN_QUESTION AQ ";
		sql += " 		ON AQ.question_id = QU.id AND AQ.STATUS != ? ";
		sql += " 	WHERE 	QU.STATUS = ? ";
		sql += " 			AND AQ.UPDATED_TIME +2 <= systimestamp ";
		sql += " 	ORDER BY AQ.UPDATED_TIME ASC ";
		params.add(QuestionsStatus.DELETED.getValue());
		params.add(QuestionsStatus.CONSIGN.getValue());
		
		return repo.getListBySQL(Questions.class, sql, params);
	}
} 
 
